﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Oracle.DataAccess.Client;
using System.Windows.Forms;
using System.Collections;
using System.Collections.Generic;


namespace DBtuning
{
    /// <summary>
    /// Summary description for FetchData
    /// </summary>
    public class FetchData
    {
        private static string Getall = "select LIBRARY_CACHE_HIT_RATIO,DICTIONARY_CACHE_HIT_RATIO,BUFFER_CACHE_HIT_RATIO,REDO_BUFFER_ALLOCATION_RETRIES,REDO_ENTRIES,REDO_LOG_BUFFER_RATIO,REDO_LOG_SPACE_WAIT_TIME,SORT_MEMORY,SORT_AREA_RATIO,ROLLBACK_SEGMENT_WAIT_RATIO from statistic where to_char(time,'yyyy-mm-dd hh24:mi:ss') between '{0}' and '{1}' order by time desc";
        private static string Get_part = "select time,{0} from statistic where to_char(time,'yyyy-mm-dd hh24:mi') between '{1}' and '{2}' order by time asc";

      
        private static string Get_span = "select to_char(time,'yyyy-mm-dd hh24:mi') as Time, LIBRARY_CACHE_HIT_RATIO, DICTIONARY_CACHE_HIT_RATIO, BUFFER_CACHE_HIT_RATIO, REDO_LOG_BUFFER_RATIO,REDO_LOG_SPACE_WAIT_TIME,SORT_AREA_RATIO, ROLLBACK_SEGMENT_WAIT_RATIO from statistic where to_char(time,'yyyy-mm-dd hh24:mi:ss') between '{0}' and '{1}' order by time desc";
        public static IDictionary<string, string> PARAMS_ROW = new Dictionary<string, string>();

        public FetchData()
        {
            
            PARAMS_ROW["Cache_Hit_Ratio_Lib"] = "LIBRARY_CACHE_HIT_RATIO";
            PARAMS_ROW["Cache_Hit_Ratio_Dic"] = "DICTIONARY_CACHE_HIT_RATIO";
            PARAMS_ROW["Buffer_Cache_Ratio"] = "BUFFER_CACHE_HIT_RATIO";
            PARAMS_ROW["Redo_Log_Buffer_Ratio"] = "REDO_LOG_BUFFER_RATIO";
            PARAMS_ROW["Redo_Log_Space_Wait"] = "REDO_LOG_SPACE_WAIT_TIME";
            PARAMS_ROW["Sort_Area_Ratio"] = "SORT_AREA_RATIO";
            PARAMS_ROW["Rollback_Segment_Wait_Ratio"] = "ROLLBACK_SEGMENT_WAIT_RATIO";
            
        }
        public DataTable GetOneday()
        {
            DataTable dt = new DataTable();
            DateTime now = DateTime.Now;
            DateTime pre = now.AddDays(-1);

            string sql = string.Format(Getall, pre.ToString("yyyy-MM-dd HH:mm:ss"), now.ToString("yyyy-MM-dd HH:mm:ss"));

            OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString);
            con.Open();

            OracleCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            adapter.Fill(dt);

            con.Close();
            return dt;
        }

        public DataTable GetDetails(string param, int interval, DateTime pre, DateTime now)
        {
            DataTable dt = new DataTable();
            string sql = string.Format(Get_part, PARAMS_ROW[param], pre.ToString("yyyy-MM-dd HH:mm:ss"), now.ToString("yyyy-MM-dd HH:mm:ss"));

            OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString);
            con.Open();

            OracleCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            adapter.Fill(dt);
            con.Close();
            DataTable table = new DataTable("param");
            table.Columns.Add("Time", typeof(String));
            table.Columns.Add("Value", typeof(String));

            string str;

            for (int i = 0; i < dt.Rows.Count; )
            {
                double avg = 0;
                string time = dt.Rows[i]["time"].ToString();
                if ((i + interval - 1) < dt.Rows.Count)
                    time = time + "---" + dt.Rows[i + interval - 1]["time"].ToString();
                else
                    time = time + "---" + dt.Rows[dt.Rows.Count - 1]["time"].ToString();
                for (int j = 0; j < interval; j++)
                {
                    avg += GetParamVal(param, dt.Rows[i]);
                    i++;
                    if (i == dt.Rows.Count)
                        break;
                }
                avg = avg / interval;

                if (avg < 1 && !Equals(param, "Redo_Log_Space_Wait"))
                {
                    avg *= 100;
                    str = avg.ToString("0.00") + "%";
                }
                else
                {
                    str = avg.ToString("0.00");
                }

                DataRow row = table.NewRow();
                row[0] = time;
                row[1] = str;
                table.Rows.Add(row);
            }
            return table;
        }

        protected double GetParamVal(string param, DataRow row)
        {
            double value = 0;
            switch (param)
            {
                case "Cache_Hit_Ratio_Lib":
                    value = Convert.ToDouble(row["LIBRARY_CACHE_HIT_RATIO"]);
                    break;
                case "Cache_Hit_Ratio_Dic":
                    value = Convert.ToDouble(row["DICTIONARY_CACHE_HIT_RATIO"]);
                    break;
                case "Buffer_Cache_Ratio":
                    value = Convert.ToDouble(row["BUFFER_CACHE_HIT_RATIO"]);
                    break;
                case "Redo_Log_Buffer_Ratio":
                    value = Convert.ToDouble(row["REDO_LOG_BUFFER_RATIO"]);
                    break;
                case "Sort_Area_Ratio":
                    value = Convert.ToDouble(row["SORT_AREA_RATIO"]);
                    break;
                case "Rollback_Segment_Wait_Ratio":
                    value = Convert.ToDouble(row["ROLLBACK_SEGMENT_WAIT_RATIO"]);
                    break;
                case "Redo_Log_Space_Wait":
                    value = Convert.ToDouble(row["REDO_LOG_SPACE_WAIT_TIME"]);
                    break;
            }
            return value;
        }

        public DataTable GetReports(DateTime from, DateTime to)
        {

            string sql = string.Format(Get_span, from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss"));

            OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString);
            con.Open();

            OracleCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            OracleDataReader read;
            read = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            DataTable table = new DataTable("Params");
            table.Columns.Add("Time", typeof(String));
            table.Columns.Add("Cache_Hit_Ratio_Lib", typeof(String));
            table.Columns.Add("Cache_Hit_Ratio_Dic", typeof(String));
            table.Columns.Add("Buffer_Cache_Ratio", typeof(String));
            table.Columns.Add("Redo_Log_Buffer_Ratio", typeof(String));
            table.Columns.Add("Redo_Log_Space_Wait", typeof(String));
            table.Columns.Add("Sort_Area_Ratio", typeof(String));
            table.Columns.Add("Rollback_Segment_Wait_Ratio", typeof(String));
            
            while (read.Read())
            {
                DataRow dr = table.NewRow();
                for (int i = 0; i < read.FieldCount; i++)
                {
                    string st = read.GetOracleValue(i).ToString();

                    if ( i < 8 && i!=5)
                    {
                        if (st[0] == '.')
                        {
                            st = "0" + st;
                            double val = Convert.ToDouble(st);
                            val *= 100;
                            st = val.ToString("0.00") + "%";
                        }
                    }
                    else if (i == 5)
                    {
                        st = "0" + st;
                        double val = Convert.ToDouble(st);
                        st = val.ToString("0.00");
                    }
                   dr[i] = st;
                }
                table.Rows.Add(dr);
            }
            con.Close();
            return table;
        }
    }
}
